/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.campaign.dao;

import com.aptech.campaign.entities.ActualBudget;
import com.aptech.campaign.entities.Campaign;
import com.aptech.campaign.entities.EstimatedBudget;
import com.aptech.campaign.entities.Product;
import com.aptech.campaign.entities.TypeCampaign;
import com.aptech.campaign.entities.TypeOffer;
import com.aptech.campaign.util.ConfigureDB;
import com.sun.rowset.CachedRowSetImpl;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.rowset.CachedRowSet;

/**
 *
 * @author Tai Nguyen
 */
public class CampaignDAO {

    ProductDAO pro = new ProductDAO();
    private String lasterror;
    String sql = null;
    Connection con = null;
    ConfigureDB db = null;
    PreparedStatement pst = null;
    CachedRowSet crs = null;

    /**
     *
     */
    public CampaignDAO() {
        db = new ConfigureDB();
    }

    /**
     *
     *
     * @return ArrayList<Product> arr
     */
    public ArrayList<Product> readByAllProduct() {
        return pro.readByAllProduct();
    }

    /**
     *
     * @return ArrayList<TypeCampaign> arr
     */
    public ArrayList<TypeCampaign> readByAllTypeCampaign() {
        ArrayList<TypeCampaign> arr = new ArrayList<TypeCampaign>();
        con = db.getConnection();
        sql = "select * from [TypeCampaign]";
        try {
            crs = new CachedRowSetImpl();
            crs.setCommand(sql);
            crs.execute(con);
            while (crs.next()) {
                TypeCampaign tc = new TypeCampaign();
                tc.setTypeID(crs.getInt(1));
                tc.setTypeName(crs.getString(2));
                tc.setCostForDay(crs.getFloat(3));
                arr.add(tc);
            }
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return arr;
    }

    /**
     *
     * @return ArrayList<TypeOffer> arr
     */
    public ArrayList<TypeOffer> readByAllTypeOffer() {
        ArrayList<TypeOffer> arr = new ArrayList<TypeOffer>();
        con = db.getConnection();
        sql = "select * from [TypeOffer]";
        try {
            crs = new CachedRowSetImpl();
            crs.setCommand(sql);
            crs.execute(con);
            while (crs.next()) {
                TypeOffer to = new TypeOffer();
                to.setOfferID(crs.getInt(1));
                to.setOfferName(crs.getString(2));
                to.setCost(crs.getFloat(3));
                arr.add(to);
            }
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return arr;
    }

    /**
     *
     * @param cam
     * @return the boolean value
     */
    public boolean createCampaign(Campaign cam) {
        con = db.getConnection();
        sql = "insert into [Campaign] values (?, ?, ?, ?, ?, ?, ?)";
        try {
            pst = con.prepareStatement(sql);
            pst.setString(1, cam.getCampaignName());
            pst.setInt(2, cam.getProductID());
            pst.setDate(3, cam.getStartDate());
            pst.setDate(4, cam.getEndDate());
            pst.setInt(5, cam.getTypeID());
            if (cam.getOfferID() > 0) {
                pst.setInt(6, cam.getOfferID());
            } else {
                pst.setNull(6, java.sql.Types.INTEGER);
            }

            pst.setBoolean(7, true);
            if (pst.executeUpdate() > 0) {
                setLasterror("Add Campaign Successful!");
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return false;
    }

    /**
     *
     * @param campaignID
     * @param campaignName
     * @param productID
     * @param startDate
     * @param endDate
     * @param typeID
     * @param offerID
     * @return boolean value
     */
    public boolean updateCampaign(int campaignID, String campaignName, int productID, Date startDate, Date endDate, int typeID, int offerID) {
        con = db.getConnection();
        sql = "update [Campaign] set CampaignName =?, ProductID = ?, StartDate=?, EndDate = ?, TypeID =?, OfferID= ? where CampaignID = ?";
        try {
            pst = con.prepareStatement(sql);
            pst.setString(1, campaignName);
            pst.setInt(2, productID);
            pst.setDate(3, startDate);
            pst.setDate(4, endDate);
            pst.setInt(5, typeID);
            if (offerID > 0) {
                pst.setInt(6, offerID);
            } else {
                pst.setNull(6, java.sql.Types.INTEGER);
            }
            pst.setInt(7, campaignID);
            if (pst.executeUpdate() > 0) {
                setLasterror("Update Campaign Successful!");
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return false;
    }

    /**
     *
     * @param campaignID
     * @return boolean value
     */
    public boolean deleteCampaign(int campaignID) {
        con = db.getConnection();
        sql = "update [Campaign] set Status = ? where CampaignID = ?";
        try {
            pst = con.prepareStatement(sql);
            pst.setBoolean(1, false);
            pst.setInt(2, campaignID);
            if (pst.executeUpdate() > 0) {
                setLasterror("Delete Campaign Successful!");
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return false;
    }

    /**
     *
     * @return ArrayList<Campaign> arr
     */
    public ArrayList<Campaign> readByAllCampaign() {
        ArrayList<Campaign> arr = new ArrayList<Campaign>();
        con = db.getConnection();
        sql = "select * from [Campaign] where Status = ?";
        try {
            crs = new CachedRowSetImpl();
            crs.setCommand(sql);
            crs.setBoolean(1, true);
            crs.execute(con);
            while (crs.next()) {
                Campaign cam = new Campaign();
                cam.setCampaignID(crs.getInt(1));
                cam.setCampaignName(crs.getString(2));
                cam.setProductID(crs.getInt(3));
                cam.setStartDate(crs.getDate(4));
                cam.setEndDate(crs.getDate(5));
                cam.setTypeID(crs.getInt(6));
                cam.setOfferID(crs.getInt(7));
                arr.add(cam);
            }
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return arr;
    }

    /**
     *
     * @param tc
     * @return the boolean value
     */
    public boolean createTypeCampaign(TypeCampaign tc) {
        con = db.getConnection();
        sql = "insert into [TypeCampaign] values(?, ?)";
        try {
            pst = con.prepareStatement(sql);
            pst.setString(1, tc.getTypeName());
            pst.setFloat(2, tc.getCostForDay());
            if (pst.executeUpdate() > 0) {
                setLasterror("Add TypeCampaign Successfyl!");
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return false;
    }

    /**
     *
     * @param to
     * @return the boolean value
     */
    public boolean createTypeOffer(TypeOffer to) {
        con = db.getConnection();
        sql = "insert into [TypeOffer] values(?, ?)";
        try {
            pst = con.prepareStatement(sql);
            pst.setString(1, to.getOfferName());
            pst.setFloat(2, to.getCost());
            if (pst.executeUpdate() > 0) {
                setLasterror("Add Offer Successful!");
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return false;
    }

    /**
     *
     * @param etb
     * @return the boolean value
     */
    public boolean inputEstimateBudget(EstimatedBudget etb) {
        con = db.getConnection();
        sql = "Insert into [EstimatedBudget] values(?, ?, ?, ?, ?, ?)";
        try {
            pst = con.prepareStatement(sql);
            pst.setInt(1, etb.getCampaignID());
            pst.setInt(2, etb.getSaleAmount());
            pst.setFloat(3, etb.getSaleTarget());
            pst.setFloat(4, etb.getBudgetAllocation());
            pst.setFloat(5, etb.getSaleVariance());
            pst.setFloat(6, etb.getBudgetVariance());
            if (pst.executeUpdate() > 0) {
                setLasterror("Save data Successful!");
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return false;
    }

    /**
     * get array campaign that dont't complete and allocate yet
     * @param
     * @return ArrayList<Campaign> arr
     */
    public ArrayList<Campaign> readByEstimate() {
        ArrayList<Campaign> arr = new ArrayList<Campaign>();
        java.sql.Date date = new java.sql.Date(System.currentTimeMillis());
        con = db.getConnection();
        sql = "select *from [Campaign] where EndDate > ? and Status = ? and CampaignID not in (select CampaignID from EstimatedBudget)";
        try {

            crs = new CachedRowSetImpl();
            crs.setCommand(sql);
            crs.setDate(1, date);
            crs.setBoolean(2, true);
            crs.execute(con);
            while (crs.next()) {
                Campaign cam = new Campaign();
                cam.setCampaignID(crs.getInt(1));
                cam.setCampaignName(crs.getString(2));
                arr.add(cam);
            }
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return arr;
    }

    /**
     * input actual budget data for a campaign has finished
     * @param ActualBudget atb
     * @return the boolean value
     */
    public boolean inputActualBudget(ActualBudget atb) {
        con = db.getConnection();
        sql = "Insert into [ActualBudget] values(?, ?, ?, ?, ?, ?)";
        try {
            pst = con.prepareStatement(sql);
            pst.setInt(1, atb.getCampaignID());
            pst.setInt(2, atb.getSoldAmount());
            pst.setFloat(3, atb.getActualSale());
            pst.setFloat(4, atb.getActualBudget());
            pst.setFloat(5, atb.getSaleVariance());
            pst.setFloat(6, atb.getBudgetVariance());
            if (pst.executeUpdate() > 0) {
                setLasterror("Save data Successful!");
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return false;
    }

    /**
     * get array campaign that completed and don't statistic yet
     * @param
     * @return ArrayList<Campaign> arr
     */
    public ArrayList<Campaign> readByActual() {
        ArrayList<Campaign> arr = new ArrayList<Campaign>();
        java.sql.Date date = new java.sql.Date(System.currentTimeMillis());
        con = db.getConnection();
        sql = "select *from [Campaign] where EndDate < ? and Status = ? and CampaignID not in (select CampaignID from ActualBudget) and CampaignID in (select CampaignID from EstimatedBudget)";
        try {

            crs = new CachedRowSetImpl();
            crs.setCommand(sql);
            crs.setDate(1, date);
            crs.setBoolean(2, true);
            crs.execute(con);
            while (crs.next()) {
                Campaign cam = new Campaign();
                cam.setCampaignID(crs.getInt(1));
                cam.setCampaignName(crs.getString(2));
                arr.add(cam);
            }
        } catch (SQLException ex) {
            Logger.getLogger(CampaignDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return arr;
    }

    /**
     *
     * @param campaignID
     * @param actualSale
     * @return the float value
     */
    public float calSaleVariance(int campaignID, float actualSale) {
        con = db.getConnection();
        sql = "select SaleTarget from [EstimatedBudget] where CampaignID = ?";
        float saleTarget;
        float saleVariance;
        try {

            crs = new CachedRowSetImpl();
            crs.setCommand(sql);
            crs.setInt(1, campaignID);
            crs.execute(con);
            while (crs.next()) {
                saleTarget = crs.getFloat(1);
                saleVariance = (actualSale - saleTarget) * 100 / saleTarget;
                return saleVariance;
            }

        } catch (SQLException ex) {
            Logger.getLogger(CampaignDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }

    /**
     *
     * @param campaignID
     * @param actualBuget
     * @return the float value
     */
    public float calbudgetVariance(int campaignID, float actualBuget) {
        con = db.getConnection();
        sql = "select BudgetAllocation from [EstimatedBudget] where CampaignID = ?";
        float budgetAllocation;
        float budgetVariance;
        try {

            crs = new CachedRowSetImpl();
            crs.setCommand(sql);
            crs.setInt(1, campaignID);
            crs.execute(con);
            while (crs.next()) {
                budgetAllocation = crs.getFloat(1);
                budgetVariance = (actualBuget - budgetAllocation) * 100 / budgetAllocation;
                return budgetVariance;
            }

        } catch (SQLException ex) {
            Logger.getLogger(CampaignDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }

    /**
     * @return the lasterror
     */
    public String getLasterror() {
        return lasterror;
    }

    /**
     * @param lasterror the lasterror to set
     */
    public void setLasterror(String lasterror) {
        this.lasterror = lasterror;
    }
}
